Distributed relational database management systems (RDMS) are well known in the art. FIG. 1 illustrates a conventional RDMS. The system includes a client 101 running an application 102, where the client 101 is coupled to a server 104 via a network 103. The server 104 includes a database engine 105, which in turn includes a query processor 106 and a data manager 107. The server 104 manages access to the database 108 by the application 102 at the client 101. Typically, Structured Query Language (SQL) queries are used to issue task requests on the database 108. The query processor 106 processes and executes these queries or statements, accessing the database 108 via the data manager 107, utilizing application programming interfaces (API).
When data in the database 108 is to be updated, the application 102 sends one or more SQL statements across the network 103 to the server 104. For example, INSERT, UPDATE, or DELETE statements may be used. Data in one or more rows of the database 108 are then inserted, updated, or deleted. Sometimes, the database 108 generates values in one or more columns in the row(s) as well. For example, a row pertaining to a sale is inserted, with values in the customer name column, payment information column, and items purchased column. The database then also inserts a timestamp in a fourth column in the same row. The database updates additional columns based on the definition of columns in the table. Values updated by a user can also be modified due to database constraints or due to triggers on the database table.
For a user to know what values were actually written to disk, separate SQL statements must be issued to fetch the data in the updated row(s), such as a SELECT statement. This requires the building of a separate execution plan for each SQL statement, and requires multiple crossings of the data manager 107. As is known in the art, an execution plan outlines how a server's query processor will actually run a specific query or statement. The query processor 106 parses and optimizes the SQL statement and generates the execution plan. The plan is then executed, using the data manager 107 to manipulate the data. These multiple API crossings between the client 102, server 104, query processor 106, and data manager 107 are inefficient. In some eases, for example, when the database generates a primary key, the only unique value in the data is the generated value. In these cases, it is not possible to retrieve the data that had just been inserted into the table since the primary key is not known by the application.
Accordingly, there exists a need for a method for generating an execution plan for updating and retrieving data from a database in a single process. The method should allow a single statement to be issued for data to be updated and retrieved from the database. The execution plan for the statement should require a single API crossing between the client and the database. The present invention addresses such a need.